import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.colors import LinearSegmentedColormap
import matplotlib.patches as patches
import numpy as np
import os

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

# === 你提供的准确文件路径 ===
DATA_FILE = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\data\erp_order_data.xlsx"
RESULTS_DIR = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\results"
os.makedirs(RESULTS_DIR, exist_ok=True)

def create_rounded_gradient_barchart():
    # 1. 读取你指定路径的数据
    print(f"正在从以下路径读取数据:\n{DATA_FILE}")
    df = pd.read_excel(DATA_FILE)
    print(f"✅ 成功加载 {len(df)} 条订单记录")

    # 2. 按商品名称汇总销售金额 (这里用 product_name 代替 "商品")
    sales_by_product = df.groupby('product_name')['product_amount'].sum().sort_values(ascending=False).head(6) # 取前6个商品
    products = sales_by_product.index.tolist()
    sales = sales_by_product.values.tolist()

    # 3. 动态生成标题
    top_product = products[0]
    top_sales = sales[0]
    bottom_product = products[-1]
    bottom_sales = sales[-1]
    title_main = "3月商品销量对比"
    title_sub = f"{top_product}销量最多，3月销量{int(top_sales)}；{bottom_product}最少，3月销量{int(bottom_sales)}"

    # 4. 创建图表（保持靛蓝背景风格）
    fig = plt.figure(figsize=(14, 9), facecolor='#1A1A2E')
    ax = fig.add_subplot(111, facecolor='#1A1A2E')

    # 渐变色
    colors = ['#16213E', '#0F3460', '#1A5F7A', '#2C8C99', '#4BB5C2', '#7FD6E3']
    cmap = LinearSegmentedColormap.from_list('ocean_blue', colors, N=100)

    bar_width = 0.5
    x_pos = np.arange(len(products))

    # 绘制圆角渐变柱子
    for i, (x, sale) in enumerate(zip(x_pos, sales)):
        # 创建一个圆角矩形作为柱子的基础
        rect = patches.FancyBboxPatch(
            (x - bar_width/2, 0), bar_width, sale,
            boxstyle="round,pad=0.02",  # 圆角
            facecolor='#2C8C99', edgecolor='#E0E0E0', linewidth=2, alpha=0.3
        )
        ax.add_patch(rect)

        # 在基础矩形上叠加渐变效果
        gradient_height = 80
        for j in range(gradient_height):
            height_segment = sale / gradient_height
            y_bottom = j * height_segment
            color = cmap(1 - (j / gradient_height) * 0.7)
            # 创建一个小的圆角矩形来模拟渐变
            small_rect = patches.FancyBboxPatch(
                (x - bar_width/2 + 0.01, y_bottom), bar_width - 0.02, height_segment,
                boxstyle="round,pad=0.01",
                facecolor=color, edgecolor='none', alpha=0.9
            )
            ax.add_patch(small_rect)

    # 5. 添加顶部数据点和标签
    for i, v in enumerate(sales):
        # 添加顶部的小圆点
        ax.plot(i, v, 'o', color='#FFFFFF', markersize=8, markeredgecolor='#E0E0E0', markeredgewidth=1)
        # 添加数据标签
        ax.text(i, v + max(sales)*0.02, f'{int(v)}', ha='center', va='bottom',
                fontsize=12, fontweight='bold', color='#FFFFFF',
                bbox=dict(boxstyle='round,pad=0.2', facecolor='#2C3E50', edgecolor='none', alpha=0.7))

    # 6. 设置样式
    ax.set_title(f"{title_main}\n{title_sub}", fontsize=20, fontweight='bold', pad=25, color='#FFFFFF')
    ax.set_ylabel('销量', fontsize=18, fontweight='bold', color='#E0E0E0')
    ax.set_xlabel('商品', fontsize=16, fontweight='bold', color='#E0E0E0')
    ax.set_xticks(x_pos)
    ax.set_xticklabels(products, fontsize=14, fontweight='bold', color='#E0E0E0')
    ax.tick_params(axis='y', labelsize=14, colors='#E0E0E0')

    for spine in ax.spines.values():
        spine.set_color('#4A4A6A')
        spine.set_linewidth(2)

    ax.grid(axis='y', alpha=0.3, linestyle='--', color='#4A4A6A')
    ax.set_axisbelow(True)
    ax.set_ylim(0, max(sales) * 1.2)

    # 7. 数据来源
    latest_date = pd.to_datetime(df['order_time']).max().strftime('%Y-%m-%d')
    ax.text(0.02, 0.98, f'*注：数据来源于公司销售系统，统计日期截至{latest_date}',
            transform=ax.transAxes, fontsize=10, color='#B0B0B0', alpha=0.7, va='top')

    plt.tight_layout()

    # 8. 保存图表
    output_path = os.path.join(RESULTS_DIR, '03_渐变圆角柱形图.png')
    plt.savefig(output_path, dpi=300, bbox_inches='tight', facecolor='#1A1A2E', edgecolor='none')
    plt.show()

    print(f"✅ 第三张图已成功保存至:\n{output_path}")

if __name__ == "__main__":
    create_rounded_gradient_barchart()